# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter

#
# class LiepinPipeline:
#     def process_item(self, item, spider):
#         return item


import pymysql
class MysqlPipeline:
    def open_spider(self,spider):
        host=spider.settings.get("MYSQL_DB_HOST")
        port = spider.settings.get("MYSQL_DB_PORT")
        dbname = spider.settings.get("MYSQL_DB_NAME")
        user = spider.settings.get("MYSQL_DB_USER")
        pwd = spider.settings.get("MYSQL_DB_PASSWORD")

        self.db_conn=pymysql.connect(host=host,port=port,db=dbname,user=user,password=pwd)    #创建数据库连接
        # 打开游标
        self.db_cur=self.db_conn.cursor()
    def process_item(self, item, spider):
        values = (
            item["p_name"],
            item["p_salary"],
            item["p_address"],
            item["p_edu"],
            item["p_time"],
            item["p_company"],
            item["p_additional"] # 与占位符%s对应的数据
        )
        # sql语句，数据部分使用占位符%s代替
        sql="insert into post(p_name,p_salary,p_address,p_edu,p_time,p_company,p_additional) value (%s,%s,%s,%s,%s,%s,%s);"    #插入数据
        self.db_cur.execute(sql,values)   #执行SQL语句
        print(sql)
        return item

    def close_spider(self, spider):
        self.db_conn.commit()  # 提交事务
        self.db_cur.close()  #关闭游标
        self.db_conn.close() #关闭数据库连接

